<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
function nz(EvalString,NullReturn)
	if (isnull(EvalString)) or (EvalString="") then
		NZ=NullReturn
	else
		NZ=EvalString
	end if
end function

FromDate=request.Form("FromDate")
FromTime=request.Form("FromTime")
ToDate=request.Form("ToDate")
ToTime=request.Form("ToTime")
Action=request.QueryString("action")

if Action<>"retrieve" then response.End()

application.Lock()

if FromDate="" then FromDate=date()
if FromTime="" then FromTime="12:00:00 AM"
if ToDate="" then ToDate=date()
if ToTime="" then ToTime="11:59:00 PM"

Fromfull=cdate(FromDate & " " & FromTime)
Tofull=cdate(ToDate & " " & ToTime)

dim Tdb,Trs,Ndb,Nrs,Cdb,Crs,entry()
set Tdb=server.CreateObject("adodb.connection")
set Trs=server.CreateObject("adodb.recordset")

Tfilepath=server.MapPath("/afterhours/access/backend/tickets.mdb")

Tdb.open ("provider=microsoft.jet.oledb.4.0;data source=" & Tfilepath)

Tsql="select * from tickets where created_time between #" & Fromfull & "# and #" & Tofull & "# order by emergency desc, status"
Trs.open Tsql,Tdb,1,2
if Trs.eof then 
	results = r
	found="do it anyway"
	ticketcount=0
	listlength=-1
else
	Tra=Trs.getrows()
	for x=0 to ubound(Tra,2)
		Tcids=Tcids & "'" & Tra(1,x) & "',"
		Tids=Tids & Tra(0,x) & ","
	next 
	Tcids=left(Tcids,len(Tcids)-1)
	Tids=left(Tids,len(Tids)-1)
	
	set Ndb=server.CreateObject("adodb.connection")
	set Cdb=server.CreateObject("adodb.connection")
	set THdb=server.CreateObject("adodb.connection")
	
	set Nrs=server.CreateObject("adodb.recordset")
	set Crs=server.CreateObject("adodb.recordset")
	set THrs=server.CreateObject("adodb.recordset")
	
	Nfilepath=server.MapPath("/afterhours/access/backend/tickets.mdb")
	Cfilepath=server.MapPath("/afterhours/access/backend/homesmart_customers.mdb")
	THfilepath=server.MapPath("/afterhours/access/backend/tickets.mdb")
	
	Ndb.open ("provider=microsoft.jet.oledb.4.0;data source=" & Nfilepath)
	Cdb.open ("provider=microsoft.jet.oledb.4.0;data source=" & Cfilepath)
	THdb.open ("provider=microsoft.jet.oledb.4.0;data source=" & THfilepath)
	
	Nsql="select * from non_Homesmart_customers where contract_id in (" & Tcids & ")"
	Csql="select * from customers where contract_id in (" & Tcids & ")"
	THsql="select * from ticket_history where ticket_id in (" & Tids & ")"
	
	Nrs.open Nsql,Ndb,1,2
	if Nrs.eof then
		Nons="none"
	else
		Nra=Nrs.getrows()
	end if
	Nrs.close
	Ndb.close
	set Nrs=nothing
	set Ndb=nothing
	
	Crs.open Csql,Cdb,1,2
	if Crs.eof then
		Custs="none"
	else
		Cra=Crs.getrows()
	end if
	Crs.close
	Cdb.close
	set Crs=nothing
	set Cdb=nothing
	
	THrs.open THsql,THdb,1,2
	if THrs.eof then
		redim THra(0,0)
		THra(0,0)=""
	else
		THra=THrs.getrows()
	end if
	THrs.close
	THdb.close
	set THrs=nothing
	set THdb=nothing
	
	'create entrys
	redim entrys(14,ubound(Tra,2))
	for y=0 to ubound(Tra,2)
		'ticket info. from Tra we take 0,2,3,4,9,6,7
		entrys(0,y)=nz(Tra(0,y),"&nbsp;")
		entrys(1,y)=nz(Tra(2,y),"&nbsp;")
		entrys(2,y)=nz(Tra(3,y),"&nbsp;")
		entrys(3,y)=nz(Tra(4,y),"&nbsp;")
		entrys(4,y)=nz(Tra(9,y),"&nbsp;")
		entrys(5,y)=nz(Tra(6,y),"&nbsp;")
		entrys(6,y)=nz(Tra(7,y),"&nbsp;")
	
		'cust info. from cust we take acct, fname lname, addr, city, zip, phone
		if isnumeric(left(Tra(1,y),1)) then 'is cust
			for z=0 to ubound(Cra,2)
				if Cra(1,z)=Tra(1,y) then
					entrys(7,y)=nz(Cra(0,z),"&nbsp;")
					entrys(8,y)=nz(Cra(2,z),"&nbsp;") & " " & nz(Cra(3,z),"&nbsp;")
					entrys(9,y)=nz(Cra(4,z),"&nbsp;")
					entrys(10,y)=nz(Cra(5,z),"&nbsp;")
					entrys(11,y)=nz(Cra(7,z),"&nbsp;")
					entrys(12,y)=nz(Cra(8,z),"&nbsp;")
					exit for
				end if
			next
		else 'is non cust
			for z=0 to ubound(Nra,2)
				if Nra(1,z)=Tra(1,y) then
					entrys(7,y)=nz(Nra(0,z),"&nbsp;")
					entrys(8,y)=nz(Nra(2,z),"&nbsp;") & " " & nz(Nra(3,z),"&nbsp;")
					entrys(9,y)=nz(Nra(4,z),"&nbsp;")
					entrys(10,y)=nz(Nra(5,z),"&nbsp;")
					entrys(11,y)=nz(Nra(7,z),"&nbsp;")
					entrys(12,y)=nz(Nra(8,z),"&nbsp;")
					exit for
				end if
			next
		end if
		
		'ticket history 2 user, 3 when, 4 note
		temp=""
		tc=0
		for z=0 to ubound(THra,2) 
			if THra(1,z)=Tra(0,y) then
				tc=tc+1
				spc=""
				for spccnt=1 to len(tc & ". ")+1
					spc=spc & "&nbsp;"
				next 
				temp=temp & "<br><b>" & tc & ". (" & THra(2,z) & " - " & THra(3,z) & ")</b><br>" & spc & "<i>" & THra(4,z) & "</i><br>" & vbcrlf
			end if
		next 
		entrys(13,y)=nz(temp,"&nbsp;")
		entrys(14,y)=nz(tc,"&nbsp;")
	next 
	listlength=ubound(entrys,2)
	ticketcount=listlength+1
end if
Trs.close
Tdb.close
set Trs=nothing
set Tdb=nothing
application.UnLock()

'0 ticket id, 1 status, 2 given to, 3 created time, 4 dispatched time, 5 appliance, 6 emergency, 7 acct number, 8 name, 9 address, 10 city, 11 zip, 12 phone, 13 ticket history, 14 num of calls
if found<>"none" then
	r=r & "<html xmlns:o='urn:schemas-microsoft-com:office:office'" & vbcrlf
	r=r & "xmlns:x='urn:schemas-microsoft-com:office:excel'" & vbcrlf
	r=r & "xmlns='http://www.w3.org/TR/REC-html40'>" & vbcrlf
	'r=r & "<head>" & vbcrlf
	r=r & "<title>Excel Report Data</title>"
	r=r & "<style>" & vbcrlf
	r=r & "<!--" & vbcrlf
	r=r & "table" & vbcrlf
	r=r & "{mso-displayed-decimal-separator:'\.';" & vbcrlf
	r=r & "mso-displayed-thousand-separator:'\,';}" & vbcrlf
	r=r & "@page" & vbcrlf
	r=r & "{margin:.5in .5in .5in .5in;" & vbcrlf
	r=r & "mso-header-margin:0in;" & vbcrlf
	r=r & "mso-footer-margin:0in;" & vbcrlf
	r=r & "mso-page-orientation:landscape;}" & vbcrlf
	r=r & "-->" & vbcrlf
	r=r & "</style>" & vbcrlf
	r=r & "<!--[if gte mso 9]><xml>" & vbcrlf
 r=r & "<x:ExcelWorkbook>" & vbcrlf
  r=r & "<x:ExcelWorksheets>" & vbcrlf
   r=r & "<x:ExcelWorksheet>" & vbcrlf
    r=r & "<x:Name>" & fromdate & " " & fromtime & " - " & todate & " " & totime & "</x:Name>" & vbcrlf
    r=r & "<x:WorksheetOptions>" & vbcrlf
     r=r & "<x:Print>" & vbcrlf
      r=r & "<x:ValidPrinterInfo/>" & vbcrlf
      r=r & "<x:PaperSizeIndex>5</x:PaperSizeIndex>" & vbcrlf
      r=r & "<x:Scale>89</x:Scale>" & vbcrlf
      r=r & "<x:HorizontalResolution>600</x:HorizontalResolution>" & vbcrlf
      r=r & "<x:VerticalResolution>600</x:VerticalResolution>" & vbcrlf
    r=r & " </x:Print>" & vbcrlf
     r=r & "<x:PageBreakZoom>60</x:PageBreakZoom>" & vbcrlf
     r=r & "<x:Selected/>" & vbcrlf
     r=r & "<x:DoNotDisplayGridlines/>" & vbcrlf
     r=r & "<x:ProtectContents>False</x:ProtectContents>" & vbcrlf
     r=r & "<x:ProtectObjects>False</x:ProtectObjects>" & vbcrlf
     r=r & "<x:ProtectScenarios>False</x:ProtectScenarios>" & vbcrlf
    r=r & "</x:WorksheetOptions>" & vbcrlf
   r=r & "</x:ExcelWorksheet>" & vbcrlf
  r=r & "</x:ExcelWorksheets>" & vbcrlf
  r=r & "<x:WindowHeight>9720</x:WindowHeight>" & vbcrlf
  r=r & "<x:WindowWidth>15195</x:WindowWidth>" & vbcrlf
  r=r & "<x:WindowTopX>0</x:WindowTopX>" & vbcrlf
  r=r & "<x:WindowTopY>30</x:WindowTopY>" & vbcrlf
  	r=r & "<x:ProtectStructure>False</x:ProtectStructure>" & vbcrlf
  	r=r & "<x:ProtectWindows>False</x:ProtectWindows>" & vbcrlf
 	r=r & "</x:ExcelWorkbook>" & vbcrlf
	r=r & "</xml><![endif]-->" & vbcrlf
	
	'r=r & "</head>" & vbcrlf
	'r=r & "<body>" & vbcrlf
	r=r & "<table border='1' width='1329' bordercolor='#000000' cellspacing='0' cellpadding='0'>" & vbcrlf
	r=r & "<tr bgcolor='#dddddd' style='font-family:Arial, Helvetica, sans-serif; font-size:14px; font-weight:bold; text-align:center'>" & vbcrlf
	r=r & "<td>Ticket#</td> <td>Emer.</td> <td>Acct#</td> <td>Name</td> <td>Address</td> <td>City</td> <td>Zip</td> <td>Phone</td> <td>Appliance</td> <td>Started</td> <td>Status</td> <td>Given To</td> <td>Dispatched Time</td> <td>Ticket History</td>" & vbcrlf
	r=r & "</tr>" & vbcrlf
	if listlength=-1 then r=r & "(No Tickets Found For This Time Period)"
	for y=0 to listlength
		r=r & "<tr style='font-family:Arial, Helvetica, sans-serif; font-size:12px'>" & vbcrlf
		r=r & "<td style='text-align:center; vertical-align:middle' width='79'>" & vbcrlf
		r=r & "     " & entrys(0,y) & vbcrlf
		r=r & "</td>" & vbcrlf
		if entrys(6,y)="Yes" then
			color="#FF0000"
		else
			color="#FFFFFF"
		end if
		r=r & "<td bgcolor='" & color & "' style='text-align:center; vertical-align:middle' width='63'>" & vbcrlf
		r=r & "     " & entrys(6,y) & vbcrlf
		r=r & "</td>" & vbcrlf
		'acct info
		r=r & "<td valign='top' width='61'>" & entrys(7,y) & "</td>" & vbcrlf 'acct #
		r=r & "<td valign='top' width='100'>" & entrys(8,y) & "</td>" & vbcrlf 'name
		r=r & "<td valign='top' width='122'>" & entrys(9,y) & "</td>" & vbcrlf 'address
		r=r & "<td valign='top' width='94'>" & entrys(10,y) & "</td>" & vbcrlf 'city
		r=r & "<td valign='top' width='42'>" & entrys(11,y) & "</td>" & vbcrlf 'zip
		r=r & "<td valign='top' width='85'>" & entrys(12,y) & "</td>" & vbcrlf 'phone
		'ticket info
		r=r & "<td valign='top' width='98'>" & entrys(5,y) & "</td>" & vbcrlf 'appliance
		r=r & "<td valign='top' width='97'>" & entrys(3,y) & "</td>" & vbcrlf 'created 
		r=r & "<td valign='top' width='68'>" & entrys(1,y) & "</td>" & vbcrlf 'status
		r=r & "<td valign='top' width='114'>" & entrys(2,y) & "</td>" & vbcrlf 'given to
		r=r & "<td valign='top' width='106'>" & entrys(4,y) & "</td>" & vbcrlf 'completed
		'ticket history
		r=r & "<td valign='top' width='200'>" & vbcrlf
			if clng(entrys(14,y))>1 then 
				plural="s"
			else
				plural=""
			end if
		r=r & "     " & entrys(14,y) & " call" & plural & "<br>" & vbcrlf
		r=r & "     " & entrys(13,y) & vbcrlf
		r=r & "</td>" & vbcrlf
		r=r & "</tr>" & vbcrlf
	next
	r=r & "<tr>" & vbcrlf
	r=r & "<td style='font-family:Arial, Helvetica, sans-serif; font-size:14px; font-weight:bold; text-align:center'>" & vbcrlf
	r=r & ticketcount & " Tickets" & vbcrlf
	r=r & "</td>" & vbcrlf
	r=r & "</tr>" & vbcrlf
	r=r & "<tr>" & vbcrlf
	r=r & "<td style='font-family:Arial, Helvetica, sans-serif; font-size:14px; font-weight:bold; text-align:center' colspan='4'>" & vbcrlf
	r=r & Fromfull & " - " & Tofull & vbcrlf
	r=r & "</td>" & vbcrlf
	r=r & "</tr>" & vbcrlf
	r=r & "</table>" & vbcrlf
	'r=r & "</body>" & vbcrlf
	r=r & "</html>"
	results=r
end if	

%>
<script>
parent.document.getElementById("ticketcount").value="<%= ticketcount %>"
</script>
<%= results %>
